
CREATE  proc regions_to_regionsTable_migration_proc AS

	DECLARE @regionId_cursor  CURSOR
	DECLARE @user_id varchar(20)
	DECLARE @region_id int
	DECLARE @new_region_id int
	DECLARE @region_name varchar(50)
	SET @regionId_cursor = CURSOR FOR SELECT User_ID, Region_ID FROM User_Table
	
	DECLARE @isTableExists int	
	DECLARE @isMigrated int
	SELECT @isTableExists = count(*) FROM sysobjects WHERE name = 'temp_region_migrate' AND type = 'U'
	IF ( @isTableExists = 0 )
	 BEGIN
		CREATE TABLE temp_region_migrate( isMigrated bit )

	 END
	ELSE
	 BEGIN
		SELECT TOP 1 @isMigrated = isMigrated FROM temp_region_migrate
	 END
 	IF ( @isMigrated is NULL )
	 BEGIN
		SET @isMigrated = 1
	 END

	IF ( @isMigrated != 0 )
	 BEGIN
		OPEN @regionId_cursor
		FETCH NEXT FROM @regionId_cursor INTO @user_id, @region_id
		
		WHILE ( @@FETCH_STATUS = 0 )
		BEGIN
			SELECT @region_name = ltrim(Region_Name) FROM Regions WHERE Region_ID = @region_id
			SELECT top 1 @new_region_id =  Region_id FROM Regions_Table WHERE ltrim(Region_Name) = @region_name
			IF ( @new_region_id IS NOT NULL AND @new_region_id > 0 )
			 BEGIN
				IF ( @region_id != @new_region_id )
				BEGIN
					UPDATE User_Table SET Region_ID = @new_region_id WHERE User_ID = @user_id
				END
			 END
			ELSE
			 BEGIN
				INSERT INTO Regions_Table( Region_Name, Deactivated ) VALUES ( @region_name , 0 )
				SELECT @new_region_id = ( SELECT @@IDENTITY )
				UPDATE User_Table SET Region_ID = @new_region_id WHERE User_ID = @user_id			
			 END 
			SET @user_id 	= NULL
			SET @region_id 	= NULL
			SET @region_name = NULL
			SET @new_region_id = NULL
	
			FETCH NEXT FROM @regionId_cursor INTO @user_id, @region_id
		END
		CLOSE @regionId_cursor
		DEALLOCATE @regionId_cursor
		INSERT INTO temp_region_migrate( isMigrated ) VALUES ( 0 )
	 END
